import { History } from '@site/src/components/History';
import { Stability } from '@site/src/components/Stability';

# Using MySQL2 with TypeScript

## Installation

```bash
npm install --save mysql2
npm install --save-dev @types/node
```

> The `@types/node` ensure the proper interaction between **TypeScript** and the **Node.js** modules used by **MySQL2** (_net_, _events_, _stream_, _tls_, etc.).

:::info
Requires **TypeScript** `>=4.5.2`.
:::

<hr />

## Usage

You can import **MySQL2** in two ways:

- By setting the `esModuleInterop` option to `true` in `tsconfig.json`

```ts
import mysql from 'mysql2';
import mysql from 'mysql2/promise';
```

- By setting the `esModuleInterop` option to `false` in `tsconfig.json`

```ts
import * as mysql from 'mysql2';
import * as mysql from 'mysql2/promise';
```

### Connection

```ts
import mysql, { ConnectionOptions } from 'mysql2';

const access: ConnectionOptions = {
  user: 'test',
  database: 'test',
};

const conn = mysql.createConnection(access);
```

### Pool Connection

```ts
import mysql, { PoolOptions } from 'mysql2';

const access: PoolOptions = {
  user: 'test',
  database: 'test',
};

const conn = mysql.createPool(access);
```

### Query and Execute

#### A simple query

```ts
conn.query('SELECT 1 + 1 AS `test`;', (_err, rows) => {
  /**
   * @rows: [ { test: 2 } ]
   */
});

conn.execute('SELECT 1 + 1 AS `test`;', (_err, rows) => {
  /**
   * @rows: [ { test: 2 } ]
   */
});
```

The `rows` output will be these possible types:

- `RowDataPacket[]`
- `RowDataPacket[][]`
- `ResultSetHeader`
- `ResultSetHeader[]`
- `ProcedureCallPacket`

In this example, you need to manually check the output types

<hr />

## Type Specification

### RowDataPacket[]

<Stability level={2} />

An array with the returned rows, for example:

```ts
import mysql, { RowDataPacket } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
});

// SELECT
conn.query<RowDataPacket[]>('SELECT 1 + 1 AS `test`;', (_err, rows) => {
  console.log(rows);
  /**
   * @rows: [ { test: 2 } ]
   */
});

// SHOW
conn.query<RowDataPacket[]>('SHOW TABLES FROM `test`;', (_err, rows) => {
  console.log(rows);
  /**
   * @rows: [ { Tables_in_test: 'test' } ]
   */
});
```

Using `rowsAsArray` option as `true`:

```ts
import mysql, { RowDataPacket } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
  rowsAsArray: true,
});

// SELECT
conn.query<RowDataPacket[]>(
  'SELECT 1 + 1 AS test, 2 + 2 AS test;',
  (_err, rows) => {
    console.log(rows);
    /**
     * @rows: [ [ 2, 4 ] ]
     */
  }
);

// SHOW
conn.query<RowDataPacket[]>('SHOW TABLES FROM `test`;', (_err, rows) => {
  console.log(rows);
  /**
   * @rows: [ [ 'test' ] ]
   */
});
```

<hr />

### RowDataPacket[][]

<Stability level={2} />

Using `multipleStatements` option as `true` with multiple queries:

```ts
import mysql, { RowDataPacket } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
  multipleStatements: true,
});

const sql = `
  SELECT 1 + 1 AS test;
  SELECT 2 + 2 AS test;
`;

conn.query<RowDataPacket[][]>(sql, (_err, rows) => {
  console.log(rows);
  /**
   * @rows: [ [ { test: 2 } ], [ { test: 4 } ] ]
   */
});
```

<hr />

### ResultSetHeader

<Stability level={2} />

<History
  records={[
    {
      version: '3.5.1',
      changes: [
        <>
          <strong>OkPacket</strong> is deprecated and might be removed in the
          future major release.
          <br />
          Please use <strong>ResultSetHeader</strong> instead.
        </>,
        <>
          <strong>changedRows</strong> option is deprecated and might be removed
          in the future major release.
          <br />
          Please use <strong>affectedRows</strong> instead.
        </>,
      ],
    },
  ]}
/>

For `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, etc.:

```ts
import mysql, { ResultSetHeader } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
});

const sql = `
  SET @1 = 1;
`;

conn.query<ResultSetHeader>(sql, (_err, result) => {
  console.log(result);
  /**
   * @result: ResultSetHeader {
      fieldCount: 0,
      affectedRows: 0,
      insertId: 0,
      info: '',
      serverStatus: 2,
      warningStatus: 0,
      changedRows: 0
    }
   */
});
```

<hr />

### ResultSetHeader[]

<Stability level={2} />

<History
  records={[
    {
      version: '3.5.1',
      changes: [
        <>
          Introduce <strong>ResultSetHeader[]</strong>
        </>,
      ],
    },
  ]}
/>

For multiples `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, etc. when using `multipleStatements` as `true`:

```ts
import mysql, { ResultSetHeader } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
  multipleStatements: true,
});

const sql = `
  SET @1 = 1;
  SET @2 = 2;
`;

conn.query<ResultSetHeader[]>(sql, (_err, results) => {
  console.log(results);
  /**
   * @results: [
      ResultSetHeader {
        fieldCount: 0,
        affectedRows: 0,
        insertId: 0,
        info: '',
        serverStatus: 10,
        warningStatus: 0,
        changedRows: 0
      },
      ResultSetHeader {
        fieldCount: 0,
        affectedRows: 0,
        insertId: 0,
        info: '',
        serverStatus: 2,
        warningStatus: 0,
        changedRows: 0
      }
    ]
   */
});
```

<hr />

### ProcedureCallPacket

<Stability level={2} />

<History
  records={[
    {
      version: '3.5.1',
      changes: [
        <>
          Introduce <strong>ProcedureCallPacket</strong>
        </>,
      ],
    },
  ]}
/>

:::tip
By performing a **Call Procedure** using `INSERT`, `UPDATE`, etc., the return will be a `ProcedureCallPacket<ResultSetHeader>` (even if you perform multiples queries and set `multipleStatements` to `true`):
:::

```ts
import mysql, { ProcedureCallPacket, ResultSetHeader } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
});

/** ResultSetHeader */
conn.query('DROP PROCEDURE IF EXISTS myProcedure');

/** ResultSetHeader */
conn.query(`
    CREATE PROCEDURE myProcedure()
    BEGIN
      SET @1 = 1;
      SET @2 = 2;
    END
  `);

/** ProcedureCallPacket */
const sql = 'CALL myProcedure()';

conn.query<ProcedureCallPacket<ResultSetHeader>>(sql, (_err, result) => {
  console.log(result);
  /**
   * @result: ResultSetHeader {
      fieldCount: 0,
      affectedRows: 0,
      insertId: 0,
      info: '',
      serverStatus: 2,
      warningStatus: 0,
      changedRows: 0
    }
   */
});
```

> For `CREATE PROCEDURE` and `DROP PROCEDURE`, these returns will be the _default_ `ResultSetHeader`.

By using `SELECT` and `SHOW` queries in a **Procedure Call**, it groups the results as:

```tsx
/** ProcedureCallPacket<RowDataPacket[]> */
[RowDataPacket[], ResultSetHeader]
```

For `ProcedureCallPacket<RowDataPacket[]>`, please see the following examples.

<hr />

### OkPacket

<Stability
  level={0}
  message={
    <>
      <strong>OkPacket</strong> is deprecated and might be removed in the future
      major release.
      <br />
      Please use <strong>ResultSetHeader</strong> instead.
    </>
  }
/>

<hr />

## Examples

You can also check some code examples using **MySQL2** and **TypeScript** to understand advanced concepts:

- [Extending and using **Interfaces** with `RowDataPacket`](/docs/examples/typescript/row-data/index)
- [Extending and using **Interfaces** with `RowDataPacket` and `rowAsArray`](/docs/examples/typescript/row-data/row-as-array)
- [Extending and using **Interfaces** with `RowDataPacket` and `multipleStatements`](/docs/examples/typescript/row-data/multi-statements)
- [Extending and using **Interfaces** with `RowDataPacket`, `rowAsArray` and `multipleStatements`](/docs/examples/typescript/row-data/row-as-array-multi-statements)
- [Checking for `ResultSetHeader`, extending and using **Interfaces** with `RowDataPacket` from `ProcedureCallPacket`](/docs/examples/typescript/procedure-call/index)
- [Checking for `ResultSetHeader`, extending and using **Interfaces** with `RowDataPacket` and `rowAsArray` from `ProcedureCallPacket`](/docs/examples/typescript/procedure-call/row-as-array)
- [Creating a basic custom **MySQL2** **Class**](/docs/examples/typescript/basic-custom-class)
